package com.campus.server.dao;

import com.campus.common.vo.Product;
import com.campus.common.util.DBUtil;

import java.sql.*;
import java.util.*;

public class ProductDao {
	//查询数据库中的所有产品信息
    public List<Product> listAllProducts() {
        List<Product> list = new ArrayList<>();
        try (Connection conn = DBUtil.getConnection(); //从DBUtil获取数据库连接
             Statement st = conn.createStatement(); //创建Statement对象用于执行SQL
             ResultSet rs = st.executeQuery("SELECT * FROM shop1")) { //执行查询，返回结果集
        	//遍历结果集的每一行
            while (rs.next()) {
                list.add(new Product(
                    rs.getInt("ID"),
                    rs.getString("name"),
                    rs.getDouble("price"),
                    rs.getInt("stock"),
                    rs.getString("description"),
                    rs.getString("image_url"),
                    rs.getString("category"),
                    rs.getInt("status"),
                    rs.getInt("hot"),
                    rs.getInt("new"),
                    rs.getInt("expire"),
                    rs.getString("expireDate")
                ));
            }
        } catch (Exception e) { e.printStackTrace(); }
        return list;
    }

    //接受产品id和减少数量，返回是否成功
    public boolean reduceStock(int id, int quantity) {
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(
                 "UPDATE shop1 SET stock = stock - ? WHERE id = ? AND stock >= ?")) {
        	conn.setAutoCommit(true);  // 确保立即提交
        	ps.setInt(1, quantity);
            ps.setInt(2, id);
            ps.setInt(3, quantity);
            int rows = ps.executeUpdate();
            System.out.println("更新行数: " + rows); // 调试用
            return rows > 0;
        } catch (Exception e) { e.printStackTrace(); }
        return false;
    }

    // 根据商品ID查询商品信息
    public Product findById(int id) {
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement("SELECT * FROM shop1 WHERE id = ?")) {
            ps.setInt(1, id);
            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                return new Product(
                    rs.getInt("ID"),
                    rs.getString("name"),
                    rs.getDouble("price"),
                    rs.getInt("stock"),
                    rs.getString("description"),
                    rs.getString("image_url"),
                    rs.getString("category"),
                    rs.getInt("status"),
                    rs.getInt("hot"),
                    rs.getInt("new"),
                    rs.getInt("expire"),
                    rs.getString("expireDate")
                );
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null; 
    }

    
    // 管理员操作： 
    // 新增商品
    public boolean insertProduct(Product p) {
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(
                 "INSERT INTO shop1(name, price, stock, description, image_url, category, status, hot, new, expire, expireDate) "
                 + "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)")) {
            ps.setString(1, p.getPname());
            ps.setDouble(2, p.getPrice());
            ps.setInt(3, p.getStock());
            ps.setString(4, p.getDesc());
            ps.setString(5, p.getImageUrl());
            ps.setString(6, p.getCategory());
            ps.setInt(7, p.getStatus());
            ps.setInt(8, p.getHot());
            ps.setInt(9, p.getNewP());
            ps.setInt(10, p.getExpireSoon());
            ps.setString(11, p.getExpireDate());
            return ps.executeUpdate() > 0;
        } catch (Exception e) { e.printStackTrace(); return false; }
    }

    // 删除商品
    public boolean deleteProductById(int id) {
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement("DELETE FROM shop1 WHERE id = ?")) {
            ps.setInt(1, id);
            return ps.executeUpdate() > 0;
        } catch (Exception e) { e.printStackTrace(); return false; }
    }

    // 修改商品信息
    public boolean updateProductInfo(Product p) {
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(
                 "UPDATE shop1 SET name=?, price=?, stock=?, description=?, image_url=?, "
                 + "category=?, status=?, hot=?, new=?, expire=?, expireDate=? WHERE id=?")) {
            ps.setString(1, p.getPname());
            ps.setDouble(2, p.getPrice());
            ps.setInt(3, p.getStock());
            ps.setString(4, p.getDesc());
            ps.setString(5, p.getImageUrl());
            ps.setString(6, p.getCategory());
            ps.setInt(7, p.getStatus());
            ps.setInt(8, p.getHot());
            ps.setInt(9, p.getNewP());
            ps.setInt(10, p.getExpireSoon());
            ps.setString(11, p.getExpireDate());
            ps.setInt(12, p.getPid());
            return ps.executeUpdate() > 0;
        } catch (Exception e) { e.printStackTrace(); return false; }
    }

    // 更新商品status
    public boolean changeProductStatus(int id, int status) {
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement("UPDATE shop1 SET status=? WHERE id=?")) {
            ps.setInt(1, status);
            ps.setInt(2, id);
            return ps.executeUpdate() > 0;
        } catch (Exception e) { e.printStackTrace(); return false; }
    }
     
    // 更新商品hot
    public boolean changeProductHot(int id, int hot) {
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement("UPDATE shop1 SET hot=? WHERE id=?")) {
            ps.setInt(1, hot);
            ps.setInt(2, id);
            return ps.executeUpdate() > 0;
        } catch (Exception e) { e.printStackTrace(); return false; }
    }
    
    // 更新商品new
    public boolean changeProductNew(int id, int newP) {
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement("UPDATE shop1 SET new=? WHERE id=?")) {
            ps.setInt(1, newP);
            ps.setInt(2, id);
            return ps.executeUpdate() > 0;
        } catch (Exception e) { e.printStackTrace(); return false; }
    }
    
    // 更新商品expire
    public boolean changeProductExpire(int id, int expire) {
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement("UPDATE shop1 SET expire=? WHERE id=?")) {
            ps.setInt(1, expire);
            ps.setInt(2, id);
            return ps.executeUpdate() > 0;
        } catch (Exception e) { e.printStackTrace(); return false; }
    }

}
